package zy.dao.shop.want.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.want.WantDAO;
import zy.dto.common.BarcodeImportDto;
import zy.dto.common.ProductDto;
import zy.dto.shop.want.ProductStorePriceDto;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.product.T_Base_Product_Shop_Price;
import zy.entity.base.size.T_Base_Size;
import zy.entity.shop.want.T_Shop_Want;
import zy.entity.shop.want.T_Shop_WantList;
import zy.entity.stock.data.T_Stock_DataBill;
import zy.entity.sys.user.T_Sys_User;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class WantDAOImpl extends BaseDaoImpl implements WantDAO{

	private String getWhereSql(Map<String, Object> params){
		String fromJsp = StringUtil.trimString(params.get("fromJsp"));
		String wt_type = StringUtil.trimString(params.get("wt_type"));
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		if (StringUtil.isNotEmpty(params.get("wt_isdraft"))) {
			sql.append(" AND wt_isdraft = :wt_isdraft ");
		}
		//审核状态，针对不同页面，作不同逻辑处理
		if("warn".equals(fromJsp)){
			if(StringUtil.isNotEmpty(params.get("wt_ar_state"))){
				sql.append(" AND wt_ar_state = :wt_ar_state ");
				if(CommonUtil.WANT_AR_STATE_NOTAPPROVE.equals(params.get("wt_ar_state"))){
					sql.append(" AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)");
					if("0".equals(wt_type)){
						if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
							sql.append(" AND wt_applyamount = 0");
						}else {
							sql.append(" AND wt_applyamount != 0");
						}
					}
				}
			}else {
				if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
					if("0".equals(wt_type)){
						sql.append(" AND (wt_ar_state IN (1,5) OR (wt_ar_state = 0 AND wt_applyamount = 0 AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)))");
					}else {
						sql.append(" AND (wt_ar_state IN (3) OR (wt_ar_state = 0 AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)))");
					}
				}else {
					if("0".equals(wt_type)){
						sql.append(" AND (wt_ar_state IN (3) OR (wt_ar_state = 0 AND wt_applyamount != 0 AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)))");
					}else {
						sql.append(" AND (wt_ar_state IN (1,5) OR (wt_ar_state = 0 AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)))");
					}
				}
			}
		}else {
			if (StringUtil.isNotEmpty(params.get("wt_ar_state"))) {
				sql.append(" AND wt_ar_state = :wt_ar_state ");
			}
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND wt_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND wt_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("wt_shop_code"))) {
			sql.append(" AND wt_shop_code = :wt_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("wt_outdp_code"))) {
			sql.append(" AND wt_outdp_code = :wt_outdp_code ");
		}
		if (StringUtil.isNotEmpty(params.get("wt_indp_code"))) {
			sql.append(" AND wt_indp_code = :wt_indp_code ");
		}
		if (StringUtil.isNotEmpty(params.get("wt_manager"))) {
			sql.append(" AND wt_manager = :wt_manager ");
		}
		if (StringUtil.isNotEmpty(params.get("wt_number"))) {
			sql.append(" AND INSTR(wt_number,:wt_number) > 0 ");
		}
		sql.append(" AND wt_type = :wt_type ");
		sql.append(" AND t.companyid=:companyid");
		return sql.toString();
	}
	
	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWhereSql(params));
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Shop_Want> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wt_id,wt_number,wt_date,wt_shop_code,wt_outdp_code,wt_indp_code,wt_maker,wt_manager,wt_handnumber,wt_applyamount,wt_sendamount,");
		sql.append(" wt_applymoney,wt_sendmoney,wt_sendcostmoney,wt_property,wt_remark,wt_ar_state,wt_ar_date,wt_ar_usid,wt_type,wt_sysdate,wt_us_id,wt_isdraft,t.companyid,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indepot_name");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司(此处由于下级店铺中只有自营店能做单据，所以可以直接查询下级所有店铺)
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWhereSql(params));
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY wt_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_Want.class));
	}

	@Override
	public T_Shop_Want load(Integer wt_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wt_id,wt_number,wt_date,wt_shop_code,wt_outdp_code,wt_indp_code,wt_maker,wt_manager,wt_handnumber,wt_applyamount,wt_sendamount,");
		sql.append(" wt_applymoney,wt_sendmoney,wt_sendcostmoney,wt_property,wt_remark,wt_ar_state,wt_ar_date,wt_ar_usid,wt_type,wt_sysdate,wt_us_id,wt_isdraft,t.companyid,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indepot_name");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE wt_id = :wt_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("wt_id", wt_id),
					new BeanPropertyRowMapper<>(T_Shop_Want.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_Want load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wt_id,wt_number,wt_date,wt_shop_code,wt_outdp_code,wt_indp_code,wt_maker,wt_manager,wt_handnumber,wt_applyamount,wt_sendamount,");
		sql.append(" wt_applymoney,wt_sendmoney,wt_sendcostmoney,wt_property,wt_remark,wt_ar_state,wt_ar_date,wt_ar_usid,wt_type,wt_sysdate,wt_us_id,wt_isdraft,t.companyid,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indepot_name");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE wt_number = :wt_number AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("wt_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_Want.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_Want check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wt_id,wt_number,wt_shop_code,wt_outdp_code,wt_indp_code,wt_applyamount,wt_ar_state,wt_ar_usid,wt_type,wt_us_id,t.companyid");
		sql.append(" FROM t_shop_want t");
		sql.append(" WHERE wt_number = :wt_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("wt_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_Want.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Shop_WantList> detail_list_forcopy(List<Long> ids) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,");
		sql.append(" ABS(wtl_applyamount) AS wtl_applyamount,ABS(wtl_sendamount) AS wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,t.companyid ");
		sql.append(" FROM t_shop_wantlist t");
		sql.append(" WHERE wtl_id IN(:ids)");
		return namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("ids", ids),
				new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}
	
	@Override
	public List<T_Shop_WantList> detail_list_forsavetemp(String wt_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_id,wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,");
		sql.append(" ABS(wtl_applyamount) AS wtl_applyamount,ABS(wtl_sendamount) AS wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,t.companyid ");
		sql.append(" FROM t_shop_wantlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY wtl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("wtl_number", wt_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}
	
	@Override
	public List<T_Shop_WantList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_id,wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,");
		sql.append(" ABS(wtl_applyamount) AS wtl_applyamount,ABS(wtl_sendamount) AS wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,t.companyid,pd_no,pd_name,pd_unit, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = wtl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = wtl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = wtl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_shop_wantlist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.wtl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY wtl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}
	
	@Override
	public List<T_Shop_WantList> detail_list_print(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_id,wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,");
		sql.append(" ABS(wtl_applyamount) AS wtl_applyamount,ABS(wtl_sendamount) AS wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = wtl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = wtl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = wtl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_shop_wantlist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.wtl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY wtl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}
	
	@Override
	public List<T_Shop_WantList> detail_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wtl_id,wtl_number,wtl_pd_code,wtl_szg_code,SUM(ABS(wtl_applyamount)) AS wtl_applyamount,SUM(ABS(wtl_sendamount)) AS wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,t.companyid,pd_no,pd_name,pd_unit,  ");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_shop_wantlist t ");
		sql.append(" JOIN t_base_product pd ON pd_code = t.wtl_pd_code AND pd.companyid = t.companyid");  
		sql.append(" WHERE 1=1 ");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY wtl_pd_code");
		sql.append(" ORDER BY wtl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}
	
	@Override
	public List<String> detail_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT wtl_szg_code");
		sql.append(" FROM t_shop_wantlist t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public List<T_Shop_WantList> temp_list_forimport(Integer wt_type,Integer us_id,Integer companyid) {//只查询商品
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_id,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_us_id,wtl_type,t.companyid ");
		sql.append(" FROM t_shop_wantlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND t.companyid = :companyid");
		
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("wtl_type", wt_type).addValue("wtl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}
	
	@Override
	public List<T_Shop_WantList> temp_list_forsave(Integer wt_type,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_id,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_us_id,wtl_type,t.companyid ");
		sql.append(" FROM t_shop_wantlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY wtl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("wtl_type", wt_type).addValue("wtl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}
	
	@Override
	public List<T_Shop_WantList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_id,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_us_id,wtl_type,t.companyid,pd_no,pd_name,pd_unit, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = wtl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = wtl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = wtl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_shop_wantlist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.wtl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY wtl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}

	@Override
	public List<T_Shop_WantList> temp_sum(Integer wt_type, Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wtl_id,wtl_pd_code,wtl_szg_code,SUM(wtl_applyamount) AS wtl_applyamount,SUM(wtl_sendamount) AS wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_us_id,wtl_type,t.companyid,pd_no,pd_name,pd_unit,  ");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_shop_wantlist_temp t ");
		sql.append(" JOIN t_base_product pd ON pd_code = t.wtl_pd_code AND pd.companyid = t.companyid");  
		sql.append(" WHERE 1=1 ");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY wtl_pd_code");
		sql.append(" ORDER BY wtl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("wtl_type", wt_type).addValue("wtl_us_id", us_id).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Shop_WantList.class));
	}

	@Override
	public List<String> temp_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT wtl_szg_code");
		sql.append(" FROM t_shop_wantlist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}

	@Override
	public Integer count_product(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		Object wt_number = param.get("wt_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT count(1)");
		sql.append(" FROM (");
		sql.append(" SELECT 1 FROM t_base_product t");
		if(StringUtil.isNotEmpty(wt_number)){
			sql.append(" LEFT JOIN t_shop_wantlist wtl ON wtl_pd_code = pd_code AND wtl.companyid = t.companyid AND wtl_number = :wt_number");
		}else {
			sql.append(" LEFT JOIN t_shop_wantlist_temp wtl ON wtl_pd_code = pd_code AND wtl.companyid = t.companyid AND wtl_type = :wt_type AND wtl_us_id = :us_id");
		}
		sql.append(" where 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND wtl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code)t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
	}

	@Override
	public List<T_Base_Product> list_product(Map<String, Object> param) {
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		Object wt_number = param.get("wt_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_id,pd_code,pd_no,pd_name,IF(wtl_id IS NULL,0,1) AS exist");
		sql.append(" FROM t_base_product t");
		if(StringUtil.isNotEmpty(wt_number)){
			sql.append(" LEFT JOIN t_shop_wantlist wtl ON wtl_pd_code = pd_code AND wtl.companyid = t.companyid AND wtl_number = :wt_number");
		}else {
			sql.append(" LEFT JOIN t_shop_wantlist_temp wtl ON wtl_pd_code = pd_code AND wtl.companyid = t.companyid AND wtl_type = :wt_type AND wtl_us_id = :us_id");
		}
		sql.append(" where 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND wtl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pd_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Product.class));
	}
	
	@Override
	public T_Base_Product load_product(String pd_code,String sp_code,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pd.pd_id,pd.pd_code,pd_no,pd_name,pd_szg_code,pd_unit,pd_year,pd_season,");
		sql.append(" f_GetProductSellPrice(pd_code,:sp_code,pd.companyid) AS pd_sell_price,pd_cost_price,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = pd.companyid LIMIT 1) AS pd_bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = pd.companyid LIMIT 1) AS pd_tp_name");
		sql.append(" ,(SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm_pd_code = pd.pd_code AND pdm.companyid = pd.companyid LIMIT 1) AS pdm_img_path");
		sql.append(" FROM t_base_product pd");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd.pd_code = :pd_code");
		sql.append(" AND pd.companyid = :companyid");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Product.class));
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	@Override
	public Map<String, Object> load_product_size(Map<String,Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//1.查询尺码信息
		List<T_Base_Size> sizes = namedParameterJdbcTemplate.query(getSizeSQL(), params, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizes);
		//2.获取颜色杯型信息
		List<ProductDto> inputs = namedParameterJdbcTemplate.query(getColorBraSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("inputs",inputs);
		//3.已录入数量
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_sz_code AS sz_code,wtl_cr_code AS cr_code,wtl_br_code AS br_code,wtl_applyamount AS amount");
		sql.append(" FROM t_shop_wantlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :pd_code");
		sql.append(" AND wtl_us_id = :us_id");
		sql.append(" AND wtl_type = :wt_type");
		sql.append(" AND companyid = :companyid");
		List<ProductDto> temps = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("temps",temps);
		//4.库存数量
		List<ProductDto> stocks = namedParameterJdbcTemplate.query(getStockSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("stocks",stocks);
		return resultMap;
	}
	
	@Override
	public Map<String, Object> load_product_size_send(Map<String,Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//1.查询尺码信息
		List<T_Base_Size> sizes = namedParameterJdbcTemplate.query(getSizeSQL(), params, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizes);
		//2.获取颜色杯型信息
		List<ProductDto> inputs = namedParameterJdbcTemplate.query(getColorBraSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("inputs",inputs);
		//3.已录入数量
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_sz_code AS sz_code,wtl_cr_code AS cr_code,wtl_br_code AS br_code,wtl_sendamount AS amount");
		sql.append(" FROM t_shop_wantlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :pd_code");
		sql.append(" AND wtl_number = :wt_number");
		sql.append(" AND companyid = :companyid");
		List<ProductDto> temps = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("temps",temps);
		//4.库存数量
		List<ProductDto> stocks = namedParameterJdbcTemplate.query(getStockSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("stocks",stocks);
		return resultMap;
	}
	
	@Override
	public T_Shop_WantList temp_queryUnitPrice(String pd_code, Integer wt_type, Integer us_id, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_unitprice");
		sql.append(" FROM t_shop_wantlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :wtl_pd_code");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("wtl_pd_code", pd_code)
							.addValue("wtl_type", wt_type)
							.addValue("wtl_us_id", us_id)
							.addValue("companyid", companyid), 
					new BeanPropertyRowMapper<>(T_Shop_WantList.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_WantList detail_queryUnitPrice(String pd_code, String wt_number, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_unitprice");
		sql.append(" FROM t_shop_wantlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :wtl_pd_code");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("wtl_pd_code", pd_code).addValue("wtl_number", wt_number).addValue("companyid", companyid), 
					new BeanPropertyRowMapper<>(T_Shop_WantList.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_WantList temp_loadBySubCode(String sub_code, Integer wt_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_id,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_us_id,wtl_type,t.companyid ");
		sql.append(" FROM t_shop_wantlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_sub_code = :wtl_sub_code");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource()
							.addValue("wtl_sub_code", sub_code)
							.addValue("wtl_type", wt_type)
							.addValue("wtl_us_id", us_id)
							.addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_WantList.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public T_Shop_WantList detail_loadBySubCode(String sub_code, String wt_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wtl_id,wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,t.companyid ");
		sql.append(" FROM t_shop_wantlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_sub_code = :wtl_sub_code");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource()
							.addValue("wtl_sub_code", sub_code)
							.addValue("wtl_number", wt_number)
							.addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_WantList.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public void temp_save(List<T_Shop_WantList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_shop_wantlist_temp");
		sql.append(" (wtl_pd_code,wtl_sub_code,wtl_szg_code,wtl_sz_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_us_id,wtl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:wtl_pd_code,:wtl_sub_code,:wtl_szg_code,:wtl_sz_code,:wtl_cr_code,:wtl_br_code,:wtl_applyamount,:wtl_sendamount,");
		sql.append(" :wtl_unitprice,:wtl_costprice,:wtl_remark,:wtl_us_id,:wtl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_save(T_Shop_WantList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_shop_wantlist_temp");
		sql.append(" (wtl_pd_code,wtl_sub_code,wtl_szg_code,wtl_sz_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_us_id,wtl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:wtl_pd_code,:wtl_sub_code,:wtl_szg_code,:wtl_sz_code,:wtl_cr_code,:wtl_br_code,:wtl_applyamount,:wtl_sendamount,");
		sql.append(" :wtl_unitprice,:wtl_costprice,:wtl_remark,:wtl_us_id,:wtl_type,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp),holder);
		temp.setWtl_id(holder.getKey().longValue());
	}
	
	@Override
	public void temp_update(List<T_Shop_WantList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist_temp");
		sql.append(" SET wtl_applyamount = :wtl_applyamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_sub_code = :wtl_sub_code");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_updateById(List<T_Shop_WantList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist_temp");
		sql.append(" SET wtl_applyamount = :wtl_applyamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_id = :wtl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_update(T_Shop_WantList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist_temp");
		sql.append(" SET wtl_applyamount = :wtl_applyamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_id = :wtl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateprice(String pd_code, Double unitPrice, Integer wt_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist_temp");
		sql.append(" SET wtl_unitprice = :wtl_unitprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :wtl_pd_code");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource()
						.addValue("wtl_unitprice", unitPrice)
						.addValue("wtl_pd_code", pd_code)
						.addValue("wtl_type", wt_type)
						.addValue("wtl_us_id", us_id)
						.addValue("companyid", companyid));
	}
	
	@Override
	public void temp_updateRemarkById(T_Shop_WantList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist_temp");
		sql.append(" SET wtl_remark = :wtl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_id = :wtl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateRemarkByPdCode(T_Shop_WantList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist_temp");
		sql.append(" SET wtl_remark = :wtl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :wtl_pd_code");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_del(List<T_Shop_WantList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_shop_wantlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_sub_code = :wtl_sub_code");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_del(Integer wtl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_wantlist_temp");
		sql.append(" WHERE wtl_id=:wtl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wtl_id", wtl_id));
	}
	
	@Override
	public void temp_delByPiCode(T_Shop_WantList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_shop_wantlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :wtl_pd_code");
		if(StringUtil.isNotEmpty(temp.getWtl_cr_code())){
			sql.append(" AND wtl_cr_code = :wtl_cr_code");
		}
		if(StringUtil.isNotEmpty(temp.getWtl_br_code())){
			sql.append(" AND wtl_br_code = :wtl_br_code");
		}
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_clear(Integer wt_type,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_shop_wantlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_type = :wtl_type");
		sql.append(" AND wtl_us_id = :wtl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("wtl_type", wt_type)
						.addValue("wtl_us_id", us_id)
						.addValue("companyid", companyid));
	}
	
	@Override
	public List<BarcodeImportDto> temp_listByImport(List<String> barCodes,String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT DISTINCT bc_pd_code,bc_subcode,bc_barcode,bc_size,pd_szg_code,bc_color,bc_bra,");
		sql.append(" f_GetProductSellPrice(pd_code,:sp_code,pd.companyid) AS unit_price,");
		sql.append(" pd_cost_price AS cost_price");
		sql.append(" FROM t_base_barcode bc");
		sql.append(" JOIN t_base_product pd ON pd.pd_code = bc_pd_code AND pd.companyid = bc.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND bc_barcode IN(:barcode)");
		sql.append(" AND bc.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("barcode", barCodes)
						.addValue("sp_code", sp_code)
						.addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(BarcodeImportDto.class));
	}

	@Override
	public void detail_save(List<T_Shop_WantList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_shop_wantlist");
		sql.append(" (wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:wtl_number,:wtl_pd_code,:wtl_sub_code,:wtl_sz_code,:wtl_szg_code,:wtl_cr_code,:wtl_br_code,:wtl_applyamount,:wtl_sendamount,:wtl_unitprice,:wtl_costprice,:wtl_remark,:wtl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void detail_save(T_Shop_WantList detail) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_shop_wantlist");
		sql.append(" (wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:wtl_number,:wtl_pd_code,:wtl_sub_code,:wtl_sz_code,:wtl_szg_code,:wtl_cr_code,:wtl_br_code,:wtl_applyamount,:wtl_sendamount,:wtl_unitprice,:wtl_costprice,:wtl_remark,:wtl_type,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(detail),holder);
		detail.setWtl_id(holder.getKey().longValue());
	}
	
	@Override
	public void detail_update(List<T_Shop_WantList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist");
		sql.append(" SET wtl_sendamount = :wtl_sendamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_sub_code = :wtl_sub_code");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void detail_update(T_Shop_WantList detail) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist");
		sql.append(" SET wtl_sendamount = :wtl_sendamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_id = :wtl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(detail));
	}
	
	@Override
	public void detail_updateprice(T_Shop_WantList detail) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist");
		sql.append(" SET wtl_unitprice = :wtl_unitprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :wtl_pd_code");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(detail));
	}
	
	@Override
	public void detail_automatch(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist");
		sql.append(" SET wtl_sendamount = wtl_applyamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_applyamount <> 0");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wtl_number", number).addValue("companyid", companyid));
	}
	
	@Override
	public void detail_del(List<T_Shop_WantList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_shop_wantlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_applyamount = 0");
		sql.append(" AND wtl_sub_code = :wtl_sub_code");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void detail_del(Integer wtl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_wantlist");
		sql.append(" WHERE wtl_id=:wtl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wtl_id", wtl_id));
	}
	
	@Override
	public void detail_updateprice(String pd_code, Double unitPrice, String wt_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_wantlist");
		sql.append(" SET wtl_unitprice = :wtl_unitprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_pd_code = :wtl_pd_code");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource()
						.addValue("wtl_unitprice", unitPrice)
						.addValue("wtl_pd_code", pd_code)
						.addValue("wtl_number", wt_number)
						.addValue("companyid", companyid));
	}
	
	@Override
	public void save(T_Shop_Want want, List<T_Shop_WantList> details, T_Sys_User user) {
		String prefix = "";
		if(want.getWt_type().equals(1)){//退货
			prefix = CommonUtil.NUMBER_PREFIX_SHOP_WANT_DPTHD + DateUtil.getYearMonthDateYYMMDD();
		}else {
			if(CommonUtil.ONE.equals(user.getShoptype()) || CommonUtil.TWO.equals(user.getShoptype())){//店铺发货单(总公司、分公司)
				prefix = CommonUtil.NUMBER_PREFIX_SHOP_WANT_DPFHD + DateUtil.getYearMonthDateYYMMDD();
			}else {
				prefix = CommonUtil.NUMBER_PREFIX_SHOP_WANT_DPBHD + DateUtil.getYearMonthDateYYMMDD();
			}
		}
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(wt_number))) AS new_number");
		sql.append(" FROM t_shop_want");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(wt_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", want.getCompanyid()), String.class);
		want.setWt_number(new_number);
		sql.setLength(0);
		sql.append("INSERT INTO t_shop_want");
		sql.append(" (wt_number,wt_date,wt_shop_code,wt_outdp_code,wt_indp_code,wt_maker,wt_manager,wt_handnumber,wt_applyamount,wt_sendamount,wt_applymoney,");
		sql.append(" wt_sendmoney,wt_sendcostmoney,wt_property,wt_remark,wt_ar_state,wt_ar_date,wt_ar_usid,wt_type,wt_sysdate,wt_us_id,wt_isdraft,companyid)");
		sql.append(" VALUES");
		sql.append(" (:wt_number,:wt_date,:wt_shop_code,:wt_outdp_code,:wt_indp_code,:wt_maker,:wt_manager,:wt_handnumber,:wt_applyamount,:wt_sendamount,:wt_applymoney,");
		sql.append(" :wt_sendmoney,:wt_sendcostmoney,:wt_property,:wt_remark,:wt_ar_state,:wt_ar_date,:wt_ar_usid,:wt_type,:wt_sysdate,:wt_us_id,:wt_isdraft,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(want),holder);
		want.setWt_id(holder.getKey().intValue());
		for(T_Shop_WantList item:details){
			item.setWtl_number(want.getWt_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_shop_wantlist");
		sql.append(" (wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:wtl_number,:wtl_pd_code,:wtl_sub_code,:wtl_sz_code,:wtl_szg_code,:wtl_cr_code,:wtl_br_code,:wtl_applyamount,:wtl_sendamount,:wtl_unitprice,:wtl_costprice,:wtl_remark,:wtl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Shop_Want want, List<T_Shop_WantList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_want");
		sql.append(" SET wt_date=:wt_date");
		sql.append(" ,wt_shop_code=:wt_shop_code");
		sql.append(" ,wt_outdp_code=:wt_outdp_code");
		sql.append(" ,wt_indp_code=:wt_indp_code");
		sql.append(" ,wt_maker=:wt_maker");
		sql.append(" ,wt_manager=:wt_manager");
		sql.append(" ,wt_handnumber=:wt_handnumber");
		sql.append(" ,wt_applyamount=:wt_applyamount");
		sql.append(" ,wt_sendamount=:wt_sendamount");
		sql.append(" ,wt_applymoney=:wt_applymoney");
		sql.append(" ,wt_sendmoney=:wt_sendmoney");
		sql.append(" ,wt_sendcostmoney=:wt_sendcostmoney");
		sql.append(" ,wt_property=:wt_property");
		sql.append(" ,wt_remark=:wt_remark");
		sql.append(" ,wt_ar_state=:wt_ar_state");
		sql.append(" ,wt_ar_date=:wt_ar_date");
		sql.append(" ,wt_ar_usid=:wt_ar_usid");
		sql.append(" ,wt_us_id=:wt_us_id");
		sql.append(" WHERE wt_id=:wt_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(want));
		for(T_Shop_WantList item:details){
			item.setWtl_number(want.getWt_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_shop_wantlist");
		sql.append(" (wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:wtl_number,:wtl_pd_code,:wtl_sub_code,:wtl_sz_code,:wtl_szg_code,:wtl_cr_code,:wtl_br_code,:wtl_applyamount,:wtl_sendamount,:wtl_unitprice,:wtl_costprice,:wtl_remark,:wtl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Shop_Want want) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_want");
		sql.append(" SET wt_ar_state=:wt_ar_state");
		sql.append(" ,wt_ar_date = :wt_ar_date");
		sql.append(" WHERE wt_id=:wt_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(want));
	}
	
	@Override
	public void updateSend(T_Shop_Want want) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT SUM(wtl_sendamount) AS wt_sendamount,");
		sql.append(" SUM(wtl_sendamount*wtl_unitprice) AS wt_sendmoney,");
		sql.append(" SUM(wtl_sendamount*wtl_costprice) AS wt_sendcostmoney");
		sql.append(" FROM t_shop_wantlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_number = :wt_number");
		sql.append(" AND companyid = :companyid");
		T_Shop_Want statWant = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(want),
				new BeanPropertyRowMapper<>(T_Shop_Want.class));
		want.setWt_sendamount(statWant.getWt_sendamount());
		want.setWt_sendmoney(statWant.getWt_sendmoney());
		want.setWt_sendcostmoney(statWant.getWt_sendcostmoney());
		sql.setLength(0);
		sql.append(" UPDATE t_shop_want");
		sql.append(" SET wt_ar_state=:wt_ar_state");
		sql.append(" ,wt_outdp_code = :wt_outdp_code");
		sql.append(" ,wt_sendamount = :wt_sendamount");
		sql.append(" ,wt_sendmoney = :wt_sendmoney");
		sql.append(" ,wt_sendcostmoney = :wt_sendcostmoney");
		sql.append(" WHERE wt_id=:wt_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(want));
	}
	
	@Override
	public void updateReceive(T_Shop_Want want) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_want");
		sql.append(" SET wt_ar_state=:wt_ar_state");
		sql.append(" ,wt_indp_code = :wt_indp_code");
		sql.append(" WHERE wt_id=:wt_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(want));
	}
	
	@Override
	public List<ProductStorePriceDto> load_want_product_price(String wt_number,String shop_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pdp_id,pdp_cost_price,pdp_vip_price,pdp_sell_price,pdp_sort_price,");
		sql.append(" pd_code,pd_sell_price,pd_vip_price,pd_sort_price,pd_cost_price,wtl_unitprice AS unitprice");
		sql.append(" FROM t_shop_wantlist t");
		sql.append(" JOIN t_base_product pd ON wtl_pd_code = pd_code AND t.companyid = pd.companyid");
		sql.append(" LEFT JOIN t_base_product_shop_price pdp ON pdp_pd_code = pd_code AND pd.companyid = pdp.companyid AND pdp_shop_code = :shop_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY wtl_pd_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("wtl_number", wt_number).addValue("shop_code", shop_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(ProductStorePriceDto.class));
	}
	
	@Override
	public void update_base_shopprice(List<T_Base_Product_Shop_Price> shopPrices_add,List<T_Base_Product_Shop_Price> shopPrices_update) {
		//更新
		StringBuffer sql = new StringBuffer("");
		if (shopPrices_update.size() > 0) {
			sql.append(" UPDATE t_base_product_shop_price");
			sql.append(" SET pdp_sell_price = :pdp_sell_price");
			sql.append(" WHERE pdp_id = :pdp_id");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(shopPrices_update.toArray()));
		}
		//保存
		if (shopPrices_add.size() > 0) {
			sql.setLength(0);
			sql.append(" INSERT INTO t_base_product_shop_price(pdp_pd_code,pdp_shop_code,pdp_cost_price,pdp_sell_price,pdp_vip_price,pdp_sort_price,companyid)");
			sql.append(" VALUES(:pdp_pd_code,:pdp_shop_code,:pdp_cost_price,:pdp_sell_price,:pdp_vip_price,:pdp_sort_price,:companyid)");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(shopPrices_add.toArray()));
		}
	}
	
	@Override
	public void updateReject(T_Shop_Want want) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_want");
		sql.append(" SET wt_ar_state=:wt_ar_state");
		sql.append(" WHERE wt_id=:wt_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(want));
	}
	
	@Override
	public void updateRejectConfirm(T_Shop_Want want) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_want");
		sql.append(" SET wt_ar_state=:wt_ar_state");
		sql.append(" ,wt_sendamount=:wt_sendamount");
		sql.append(" ,wt_sendmoney=:wt_sendmoney");
		sql.append(" ,wt_sendcostmoney=:wt_sendcostmoney");
		sql.append(" WHERE wt_id=:wt_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(want));
	}
	
	@Override
	public List<T_Stock_DataBill> listStock(String number,String dp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wtl_pd_code AS sd_pd_code,wtl_sub_code AS sd_code,wtl_cr_code AS sd_cr_code,wtl_sz_code AS sd_sz_code,");
		sql.append(" wtl_br_code AS sd_br_code,SUM(ABS(wtl_sendamount)) AS bill_amount,sd_id,sd_amount");
		sql.append(" FROM t_shop_wantlist wtl");
		sql.append(" LEFT JOIN t_stock_data sd ON wtl_sub_code = sd_code AND wtl.companyid = sd.companyid AND sd.sd_dp_code = :dp_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND wtl_number = :wtl_number");
		sql.append(" AND wtl.companyid = :companyid");
		sql.append(" GROUP BY wtl_sub_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("wtl_number", number).addValue("dp_code", dp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_DataBill.class));
	}
	
	@Override
	public void del(String wt_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_want");
		sql.append(" WHERE wt_number=:wt_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wt_number", wt_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_shop_wantlist");
		sql.append(" WHERE wtl_number=:wtl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wtl_number", wt_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String wt_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_wantlist");
		sql.append(" WHERE wtl_number=:wtl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wtl_number", wt_number).addValue("companyid", companyid));
	}
	
}
